package me.msz.learning.rest.dao.impl;

import ch.qos.logback.classic.LoggerContext;
import ch.qos.logback.core.util.StatusPrinter;
import me.msz.learning.rest.beans.Book;
import me.msz.learning.rest.dao.BookDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Erich on 2016/1/7.
 */
public class BookDAOImplH2JDBC implements BookDAO {
	public static BookDAOImplH2JDBC getInstance() {
		return ourInstance;
	}

//	private void closeAutoCloseable(AutoCloseable resource) {
//		try {
//			if (resource != null) {
//				resource.close();
//				System.out.println("Resource " + resource.getClass().getSimpleName() + " closed!");
//			} else {
//				System.out.println("Resource " + resource.getClass().getSimpleName() + " has been closed!");
//			}
//		} catch(Exception e) {
//			e.printStackTrace();
//		}
//	}

	@Override
	public void createBook(Book book) {
		try {
			Connection conn = DriverManager.getConnection(this.dbUrl, this.dbUser, this.dbUserPwd);

			String sql = "INSERT INTO BOOK(TITLE, AUTHORS, PRICE) VALUES(?, ?, ?)";
			PreparedStatement pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, book.getTitle());
			pStmt.setString(2, book.getAuthors());
			pStmt.setDouble(3, book.getPrice());

			int rowsAffected = pStmt.executeUpdate();
			pStmt.close();
			conn.close();

			String[] items = {book.getTitle(), book.getAuthors(), String.valueOf(book.getPrice())};
			logger.debug("QUERY Statement \"{}\" is executed.", formatSqlString(sql, items));
			logger.debug(rowsAffected + " row(s) was affected.");
		} catch (SQLException sqlE) {
			sqlE.printStackTrace();
		}
	}

	@Override
	public List<Book> getByTitile(String title) {
		List<Book> retBooks = new ArrayList<Book>();
		try {
			Connection conn = DriverManager.getConnection(this.dbUrl, this.dbUser, this.dbUserPwd);

			String sql = "SELECT * FROM BOOK WHERE TITLE = ?";
			PreparedStatement pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, title);

			ResultSet rs = pStmt.executeQuery();
			generateBookList(retBooks, rs);
			rs.close();
			pStmt.close();
			conn.close();

			String[] items = {title};
			logger.debug("QUERY Statement \"{}\" is executed.", formatSqlString(sql, items));
		} catch (SQLException sqlE) {
			sqlE.printStackTrace();
		}
		return retBooks;
	}

	@Override
	public List<Book> getAllBooks() {
		List<Book> retBooks = new ArrayList<Book>();
		try {
			Connection conn = DriverManager.getConnection(this.dbUrl, this.dbUser, this.dbUserPwd);

			String sql = "SELECT * FROM BOOK";
			PreparedStatement pStmt = conn.prepareStatement(sql);

			ResultSet rs = pStmt.executeQuery();
			generateBookList(retBooks, rs);
			rs.close();
			pStmt.close();
			conn.close();
			logger.debug("QUERY Statement \"{}\" is executed.", sql);
		} catch (SQLException sqlE) {
			sqlE.printStackTrace();
		}
		return retBooks;
	}

	@Override
	public void updateBook(Book book) {
		try {
			Connection conn = DriverManager.getConnection(this.dbUrl, this.dbUser, this.dbUserPwd);

			String sql = "UPDATE BOOK SET AUTHORS=?, PRICE=? WHERE TITLE=?";
			PreparedStatement pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, book.getAuthors());
			pStmt.setDouble(2, book.getPrice());
			pStmt.setString(3, book.getTitle());

			int rowsAffected = pStmt.executeUpdate();
			pStmt.close();
			conn.close();

			String[] items = {book.getAuthors(), String.valueOf(book.getPrice()), book.getTitle()};
			logger.debug("QUERY Statement \"{}\" is executed.", formatSqlString(sql, items));
			logger.debug(rowsAffected + " row(s) was affected.");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void deleteBook(Book book) {
		try {
			Connection conn = DriverManager.getConnection(this.dbUrl, this.dbUser, this.dbUserPwd);

			String sql = "DELETE BOOK WHERE TITLE=?";
			PreparedStatement pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, book.getTitle());

			int rowsAffected = pStmt.executeUpdate();
			pStmt.close();
			conn.close();

			String[] items = {book.getTitle()};
			logger.debug("QUERY Statement \"{}\" is executed.", formatSqlString(sql, items));
			logger.debug(rowsAffected + " row(s) was affected.");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void deleteAllBooks() {
		try {
			Connection conn = DriverManager.getConnection(this.dbUrl, this.dbUser, this.dbUserPwd);

			String sql = "DELETE BOOK";
			PreparedStatement pStmt = conn.prepareStatement(sql);

			int rowsAffected = pStmt.executeUpdate();
			pStmt.close();
			conn.close();
			logger.debug("QUERY Statement \"{}\" is executed.", sql);
			logger.debug(rowsAffected + " row(s) was affected.");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private BookDAOImplH2JDBC() {
		try {
			Class.forName("org.h2.Driver");
			this.dbUrl = "jdbc:h2:~/h2/test";
			this.dbUser = "admin";
			this.dbUserPwd = "admin";
			logger = LoggerFactory.getLogger(me.msz.learning.rest.dao.impl.BookDAOImplH2JDBC.class);
			LoggerContext lc = (LoggerContext) LoggerFactory.getILoggerFactory();
			StatusPrinter.print(lc);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void generateBookList(List<Book> retBooks, ResultSet rs) throws SQLException {
		if (rs.first()) {
			do {
				Book retBook = new Book();
				retBook.setTitle(rs.getString("TITLE"));
				retBook.setAuthors(rs.getString("AUTHORS"));
				retBook.setPrice(rs.getDouble("PRICE"));
				retBooks.add(retBook);
			} while (rs.next());
		}
	}

	private String formatSqlString(String sqlWithQMark, String[] items) {
		String retStr = sqlWithQMark;
		int i = 0;
		while (retStr.contains("?")) {
			retStr = retStr.replaceFirst("\\?", items[i]);
			i++;
			if (i >= items.length) {
				break;
			}
		}
		return retStr;
	}

	private static BookDAOImplH2JDBC ourInstance = new BookDAOImplH2JDBC();
	private String dbUrl;
	private String dbUser;
	private String dbUserPwd;
	private Logger logger;
}
